﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using CardLogistics.Model.CardMaking;
using System.Data.OracleClient;

namespace CardLogistics.DAL.CardMaking
{
    /// <summary>
    /// 卡片制作_白卡出库数据访问类
    /// </summary>
    public partial class CardMaking_CardOutStorageData
    {
        #region 查询
        /// <summary>
        /// 根据条件查询相应的表的字段
        /// </summary>
        /// <param name="StrWhere">查询条件</param>
        /// <param name="StrFields">要查询的字段</param>
        /// <returns>填充完数据的DataTable</returns>
        public DataTable GetList(string StrWhere, string StrFields)
        {
            return CommonData.GetListOrderBy(StrWhere, "v_CardMaking_CardOutStorage", StrFields, "ID", 0);
        }

        /// <summary>
        /// 根据编号查询基本信息
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns>填充完数据的DataTable</returns>
        public DataTable GetInfoById(string id)
        {
            return GetList(" ID = '" + id + "' ", "*");
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public CardMaking_CardOutStorage GetModel(string rId)
        {
            CardMaking_CardOutStorage model = new CardMaking_CardOutStorage();
            DataRow row = GetInfoById(rId).Rows[0];
            if (row != null)
            {
                if (row["ID"] != null && row["ID"].ToString() != "")
                {
                    model.ID = row["ID"].ToString();
                }
                if (row["OUTSTORAGENO"] != null && row["OUTSTORAGENO"].ToString() != "")
                {
                    model.OUTSTORAGENO = row["OUTSTORAGENO"].ToString();
                }
                if (row["DEMANDID"] != null && row["DEMANDID"].ToString() != "")
                {
                    model.DEMANDID = row["DEMANDID"].ToString();
                }
                if (row["OUTSTORAGEDATE"] != null && row["OUTSTORAGEDATE"].ToString() != "")
                {
                    model.OUTSTORAGEDATE = DateTime.Parse(row["OUTSTORAGEDATE"].ToString());
                }
                if (row["STOCKCONTROLLER"] != null && row["STOCKCONTROLLER"].ToString() != "")
                {
                    model.STOCKCONTROLLER = row["STOCKCONTROLLER"].ToString();
                }
                if (row["REMARKS"] != null && row["REMARKS"].ToString() != "")
                {
                    model.REMARKS = row["REMARKS"].ToString();
                }
                if (row["LASTUPDATEUSER"] != null && row["LASTUPDATEUSER"].ToString() != "")
                {
                    model.LASTUPDATEUSER = row["LASTUPDATEUSER"].ToString();
                }
                if (row["LASTUPDATEDATE"] != null && row["LASTUPDATEDATE"].ToString() != "")
                {
                    model.LASTUPDATEDATE = DateTime.Parse(row["LASTUPDATEDATE"].ToString());
                }
                if (row["ADDTIME"] != null && row["ADDTIME"].ToString() != "")
                {
                    model.ADDTIME = DateTime.Parse(row["ADDTIME"].ToString());
                }
                if (row["ISSUBMIT"] != null && row["ISSUBMIT"].ToString() != "")
                {
                    model.ISSUBMIT = decimal.Parse(row["ISSUBMIT"].ToString());
                }
                if (row["SUPPID"] != null && row["SUPPID"].ToString() != "")
                {
                    model.SUPPID = row["SUPPID"].ToString();
                }
                if (row["CARDTYPEID"] != null && row["CARDTYPEID"].ToString() != "")
                {
                    model.CARDTYPEID = row["CARDTYPEID"].ToString();
                }

                #region 添加便于查询的字段
                if (row["DemandNO"] != null && row["DemandNO"].ToString() != "")
                {
                    model.DemandNO = row["DemandNO"].ToString();
                }
                if (row["suppliersname"] != null && row["suppliersname"].ToString() != "")
                {
                    model.suppliersname = row["suppliersname"].ToString();
                }
                if (row["cardtypename"] != null && row["cardtypename"].ToString() != "")
                {
                    model.cardtypename = row["cardtypename"].ToString();
                }
                //离盒单号Id
                if (row["SeparateId"] != null && row["SeparateId"].ToString() != "")
                {
                    model.SeparateId = row["SeparateId"].ToString();
                }
                //离盒单号
                if (row["SeparateNo"] != null && row["SeparateNo"].ToString() != "")
                {
                    model.SeparateNo = row["SeparateNo"].ToString();
                }
                #endregion
            }
            return model;
        }
        #endregion

        #region 新增
        /// <summary>
        /// 新增信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Add(CardMaking_CardOutStorage model)
        {
            try
            {
                OracleParameter[] parameters = {
                    new OracleParameter("v_varOut", OracleType.NVarChar,200),
                    new OracleParameter("V_OUTSTORAGENO", OracleType.NVarChar),
                    new OracleParameter("V_DEMANDID", OracleType.NVarChar),
                    new OracleParameter("V_OUTSTORAGEDATE", OracleType.DateTime),
                    new OracleParameter("V_STOCKCONTROLLER", OracleType.NVarChar),
                    new OracleParameter("V_REMARKS", OracleType.NVarChar),
                    new OracleParameter("V_LASTUPDATEUSER", OracleType.VarChar,50),
                    new OracleParameter("V_LASTUPDATEDATE", OracleType.DateTime),
                    new OracleParameter("V_ADDTIME", OracleType.DateTime),
                    new OracleParameter("V_ISSUBMIT", OracleType.Number,4),
                    new OracleParameter("V_SUPPID", OracleType.NVarChar),
                    new OracleParameter("V_CARDTYPEID", OracleType.NVarChar),
                    new OracleParameter("V_SeparateId", OracleType.NVarChar)};
                parameters[0].Direction = ParameterDirection.Output;
                parameters[1].Value = model.OUTSTORAGENO;
                parameters[2].Value = model.DEMANDID;
                parameters[3].Value = model.OUTSTORAGEDATE;
                parameters[4].Value = model.STOCKCONTROLLER;
                parameters[5].Value = model.REMARKS;
                parameters[6].Value = model.LASTUPDATEUSER;
                parameters[7].Value = model.LASTUPDATEDATE;
                parameters[8].Value = model.ADDTIME;
                parameters[9].Value = model.ISSUBMIT;
                parameters[10].Value = model.SUPPID;
                parameters[11].Value = model.CARDTYPEID;
                parameters[12].Value = model.SeparateId;

                string StrOutPar = DbHelperSQL.RunProcedure("P_CardMaking_CardOutStor_ADD", parameters, parameters[0]).ToString();
                return StrOutPar;
            }
            catch (Exception ex)
            {
                return "err," + ex.Message;
            }
        }
        #endregion

        #region  重载新增信息
        /// <summary>
        /// 重载新增信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Add(CardMaking_CardOutStorage model, string FileValue, string rkId)
        {
            string StrOutPar = string.Empty;
            int val = 0;
            string sqlConn = DbHelperSQL.ConnectionString;
            using (OracleConnection conn = new OracleConnection(sqlConn))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;

                string[] split = FileValue.Split(';');//以分号分隔
                int count = split.Length; //计算行数
                string outStorageId = System.Guid.NewGuid().ToString();//主键ID
                try
                {
                    StringBuilder strSql = new StringBuilder(" begin ");
                    for (int i = 0; i < count; i++)
                    {
                        string BOXNO = split[i].Split('|')[0];
                        string CARDNO = split[i].Split('|')[1];
                        string STARTNO = split[i].Split('|')[2];
                        string ENDNO = split[i].Split('|')[3];
                        string DUENUMBER = split[i].Split('|')[4];
                        string ACTUALNUMBER = split[i].Split('|')[5];
                        string MisCard = split[i].Split('|')[6];

                        strSql.Append("insert into cardmaking_cardoutstrgedetail");
                        strSql.Append("(OUTSTORAGEID,BOXNO,CARDNO,STARTNO,ENDNO,DUENUMBER,ACTUALNUMBER,MISSINGCARDNO)");
                        strSql.Append(" values(");
                        strSql.Append("'" + outStorageId + "',");
                        strSql.Append("" + BOXNO + "," + CARDNO + ", " + STARTNO + ", " + ENDNO + ", " + DUENUMBER + ", " + ACTUALNUMBER + ",");
                        //strSql.Append(" Fun_GetMisCard(" + STARTNO + "," + ENDNO + ",'" + rkId + "','') ");
                        strSql.Append("" + MisCard + " ");
                        strSql.Append(");");

                        #region 插入卡明细
                        strSql.Append("P_CARDRECORD_Add(" + STARTNO + "," + ENDNO + ",");
                        strSql.Append("'0',0,'" + outStorageId + "',to_date('" + model.OUTSTORAGEDATE.ToString() + "','yyyy-MM-dd HH24:mi:ss'),");
                        strSql.Append("1," + i + ",0);");
                        #endregion
                    }
                    strSql.Append(" end; ");
                    if (!String.IsNullOrEmpty(strSql.ToString()))
                    {
                        cmd.CommandText = strSql.ToString();
                        val += cmd.ExecuteNonQuery();
                    }
                    OracleParameter[] parameters = {
                    new OracleParameter("v_varOut", OracleType.NVarChar,200),
                    new OracleParameter("V_OUTSTORAGENO", OracleType.NVarChar),
                    new OracleParameter("V_DEMANDID", OracleType.NVarChar),
                    new OracleParameter("V_OUTSTORAGEDATE", OracleType.DateTime),
                    new OracleParameter("V_STOCKCONTROLLER", OracleType.NVarChar),
                    new OracleParameter("V_REMARKS", OracleType.NVarChar),
                    new OracleParameter("V_LASTUPDATEUSER", OracleType.VarChar,50),
                    new OracleParameter("V_LASTUPDATEDATE", OracleType.DateTime),
                    new OracleParameter("V_ADDTIME", OracleType.DateTime),
                    new OracleParameter("V_ISSUBMIT", OracleType.Number,4),
                    new OracleParameter("V_SUPPID", OracleType.NVarChar),
                    new OracleParameter("V_CARDTYPEID", OracleType.NVarChar),
                    new OracleParameter("V_SeparateId", OracleType.NVarChar),
                    new OracleParameter("VID", OracleType.VarChar),};
                    parameters[0].Direction = ParameterDirection.Output;
                    parameters[1].Value = model.OUTSTORAGENO;
                    parameters[2].Value = model.DEMANDID;
                    parameters[3].Value = model.OUTSTORAGEDATE;
                    parameters[4].Value = model.STOCKCONTROLLER;
                    parameters[5].Value = model.REMARKS;
                    parameters[6].Value = model.LASTUPDATEUSER;
                    parameters[7].Value = model.LASTUPDATEDATE;
                    parameters[8].Value = model.ADDTIME;
                    parameters[9].Value = model.ISSUBMIT;
                    parameters[10].Value = model.SUPPID;
                    parameters[11].Value = model.CARDTYPEID;
                    parameters[12].Value = model.SeparateId;
                    parameters[13].Value = outStorageId;


                    StrOutPar = DbHelperSQL.RunProcedure("P_CardMaking_CardOutStor_ADD", parameters, parameters[0]).ToString();
                    if (StrOutPar.Split(',')[0] == "ok")
                    {
                        tx.Commit();
                    }
                    else
                        tx.Rollback();
                    return StrOutPar;
                }
                catch (System.Data.OracleClient.OracleException e)
                {
                    tx.Rollback();
                    throw new Exception(e.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
        }
        #endregion

        #region 修改
        /// <summary>
        /// 修改信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Update(CardMaking_CardOutStorage model)
        {
            try
            {
                OracleParameter[] parameters = {
                    new OracleParameter("V_ID", OracleType.NVarChar),
                    new OracleParameter("V_OUTSTORAGENO", OracleType.NVarChar),
                    new OracleParameter("V_DEMANDID", OracleType.NVarChar),
                    new OracleParameter("V_OUTSTORAGEDATE", OracleType.DateTime),
                    new OracleParameter("V_STOCKCONTROLLER", OracleType.NVarChar),
                    new OracleParameter("V_REMARKS", OracleType.NVarChar),
                    new OracleParameter("V_LASTUPDATEUSER", OracleType.VarChar,50),
                    new OracleParameter("V_LASTUPDATEDATE", OracleType.DateTime),
                    new OracleParameter("V_ADDTIME", OracleType.DateTime),
                    new OracleParameter("V_ISSUBMIT", OracleType.Number,4),
                    new OracleParameter("V_SUPPID", OracleType.NVarChar),
                    new OracleParameter("V_CARDTYPEID", OracleType.NVarChar),
                    new OracleParameter("V_SeparateId", OracleType.NVarChar),
                    new OracleParameter("v_varOut", OracleType.VarChar,50)
                                               };
                parameters[0].Value = model.ID;
                parameters[1].Value = model.OUTSTORAGENO;
                parameters[2].Value = model.DEMANDID;
                parameters[3].Value = model.OUTSTORAGEDATE;
                parameters[4].Value = model.STOCKCONTROLLER;
                parameters[5].Value = model.REMARKS;
                parameters[6].Value = model.LASTUPDATEUSER;
                parameters[7].Value = model.LASTUPDATEDATE;
                parameters[8].Value = model.ADDTIME;
                parameters[9].Value = model.ISSUBMIT;
                parameters[10].Value = model.SUPPID;
                parameters[11].Value = model.CARDTYPEID;
                parameters[12].Value = model.SeparateId;
                parameters[13].Direction = ParameterDirection.Output;

                string StrOutPar = DbHelperSQL.RunProcedure("P_CardMaking_CardOutStor_Upd", parameters, parameters[13]).ToString();
                return StrOutPar;
            }
            catch (Exception ex)
            {
                return "err," + ex.Message;
            }
        }
        #endregion

        #region 重载修改信息
        /// <summary>
        /// 重载修改信息
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns>返回插入数据的编号</returns>
        public string Update(CardMaking_CardOutStorage model, string FileValue, string rkId)
        {
            string StrOutPar = string.Empty;
            int val = 0;
            string sqlConn = DbHelperSQL.ConnectionString;
            using (OracleConnection conn = new OracleConnection(sqlConn))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;

                string[] split = FileValue.Split(';');//以分号分隔
                int count = split.Length; //计算行数

                try
                {
                    StringBuilder strSql = new StringBuilder(" begin ");
                    strSql.Append(" delete from cardmaking_cardoutstrgedetail where OUTSTORAGEID ='" + model.ID + "';");//删除已入库明细
                    strSql.Append("  update CARDRECORD set BKCHUKUSJ=null,BKCHUKUID=null where BKCHUKUID='" + model.ID + "'; ");//还原卡流水
                    for (int i = 0; i < count; i++)
                    {
                        string BOXNO = split[i].Split('|')[0];
                        string CARDNO = split[i].Split('|')[1];
                        string STARTNO = split[i].Split('|')[2];
                        string ENDNO = split[i].Split('|')[3];
                        string DUENUMBER = split[i].Split('|')[4];
                        string ACTUALNUMBER = split[i].Split('|')[5];

                        strSql.Append("insert into cardmaking_cardoutstrgedetail");
                        strSql.Append("(OUTSTORAGEID,BOXNO,CARDNO,STARTNO,ENDNO,DUENUMBER,ACTUALNUMBER,MISSINGCARDNO)");
                        strSql.Append(" values(");
                        strSql.Append("'" + model.ID + "',");
                        strSql.Append("" + BOXNO + "," + CARDNO + ", " + STARTNO + ", " + ENDNO + ", " + DUENUMBER + ", " + ACTUALNUMBER + ",");
                        strSql.Append(" Fun_GetMisCard(" + STARTNO + "," + ENDNO + ",'" + rkId + "','') ");
                        strSql.Append(");");

                        #region 插入卡明细
                        strSql.Append("P_CARDRECORD_Add(" + STARTNO + "," + ENDNO + ",");
                        strSql.Append("'0',0,'" + model.ID + "',to_date('" + model.OUTSTORAGEDATE.ToString() + "','yyyy-MM-dd HH24:mi:ss'),");
                        strSql.Append("1," + i + ",0);");
                        #endregion
                    }
                    strSql.Append(" end; ");
                    if (!String.IsNullOrEmpty(strSql.ToString()))
                    {
                        cmd.CommandText = strSql.ToString();
                        val += cmd.ExecuteNonQuery();
                    }
                    OracleParameter[] parameters = {
                    new OracleParameter("V_ID", OracleType.NVarChar),
                    new OracleParameter("V_OUTSTORAGENO", OracleType.NVarChar),
                    new OracleParameter("V_DEMANDID", OracleType.NVarChar),
                    new OracleParameter("V_OUTSTORAGEDATE", OracleType.DateTime),
                    new OracleParameter("V_STOCKCONTROLLER", OracleType.NVarChar),
                    new OracleParameter("V_REMARKS", OracleType.NVarChar),
                    new OracleParameter("V_LASTUPDATEUSER", OracleType.VarChar,50),
                    new OracleParameter("V_LASTUPDATEDATE", OracleType.DateTime),
                    new OracleParameter("V_ADDTIME", OracleType.DateTime),
                    new OracleParameter("V_ISSUBMIT", OracleType.Number,4),
                    new OracleParameter("V_SUPPID", OracleType.NVarChar),
                    new OracleParameter("V_CARDTYPEID", OracleType.NVarChar),
                    new OracleParameter("V_SeparateId", OracleType.NVarChar),
                    new OracleParameter("v_varOut", OracleType.VarChar,50)};
                    parameters[0].Value = model.ID;
                    parameters[1].Value = model.OUTSTORAGENO;
                    parameters[2].Value = model.DEMANDID;
                    parameters[3].Value = model.OUTSTORAGEDATE;
                    parameters[4].Value = model.STOCKCONTROLLER;
                    parameters[5].Value = model.REMARKS;
                    parameters[6].Value = model.LASTUPDATEUSER;
                    parameters[7].Value = model.LASTUPDATEDATE;
                    parameters[8].Value = model.ADDTIME;
                    parameters[9].Value = model.ISSUBMIT;
                    parameters[10].Value = model.SUPPID;
                    parameters[11].Value = model.CARDTYPEID;
                    parameters[12].Value = model.SeparateId;
                    parameters[13].Direction = ParameterDirection.Output;
                    StrOutPar = DbHelperSQL.RunProcedure("P_CardMaking_CardOutStor_Upd", parameters, parameters[13]).ToString();

                    if (StrOutPar.Split(',')[0] == "ok")
                    {
                        tx.Commit();
                    }
                    else
                        tx.Rollback();
                    return StrOutPar;
                }
                catch (System.Data.OracleClient.OracleException e)
                {
                    tx.Rollback();
                    return StrOutPar;
                    throw new Exception(e.Message);
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
        }
        #endregion

        #region 删除
        /// <summary>
        /// 删除信息(同时删除白卡出库明细)
        /// </summary>
        /// <param name="id">删除信息的ID</param>
        /// <returns>删除成功返回True。否则返回false</returns>
        public string Delete(string id)
        {
            try
            {
                OracleParameter[] parameters = {
                        new OracleParameter("VarOut",  OracleType.VarChar,100),
                        new OracleParameter("vid",  OracleType.VarChar,50)
                };
                parameters[0].Direction = ParameterDirection.Output;
                parameters[1].Value = id;
                string StrOutPar = DbHelperSQL.RunProcedure("P_CardMaking_CardOutStor_Del", parameters, parameters[0]).ToString();
                return StrOutPar;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 返回供应商、卡类型、盒号参数
        /// <summary>
        /// 获取系统中有卡的 盒号、卡类型、供应商信息
        /// </summary>
        /// <param name="OUTSTORAGEID">编辑时需要</param>
        /// <returns></returns>
        public DataTable GetAllInfo(string OUTSTORAGEID)
        {
            StringBuilder sbInfo = new StringBuilder();
            sbInfo.Append(" select c.boxno,c.cardno,c.actualnumber-nvl(d.actualnumber,0) synum,");
            sbInfo.Append(" case length(d.lendno) when 8 then to_number(lendno)+1 else to_number(c.startno) end startno,");//获取下次出库开始卡号
            sbInfo.Append(" c.endno,c.Cardtypename,c.Cardtypeid,c.suppliersname,c.suppid,c.storageType,c.checkid from ");
            sbInfo.Append(" (select t.boxno,t.cardno,t.startno,t.endno,t.ACTUALNUMBER,t.Cardtypename,t.Cardtypeid,t.suppliersname,t.suppid,t.storagetype,t.checkid");
            sbInfo.Append(" from v_storage_CHECK_card_detailed t where t.issubmit=5");
            //入库类型不等于二次利用入库 //and t.storagetype !='AC537E666ADC46199AA742AAA5D43B2E' and t.storagetype !='4B1025E43FEE4F1D89FED74437D80974' and t.storagetype !='D0B4A3D7CE5D43F29048CA3692D4F56D' 缺卡入库 备品入库 赠品入库
            sbInfo.Append(" and (t.storagetype !='5DDA8F0A65AE4BDDA21A622EA6E1F257') ");
            sbInfo.Append(" ) c left join ");
            sbInfo.Append(" (select a.cardno,sum(a.actualnumber) actualnumber,nvl(max(a.endno),'') lendno,min(b.issubmit) issubmit from cardmaking_cardoutstrgedetail a");
            sbInfo.Append(" left join cardmaking_cardoutstorage b on a.outstorageid=b.id where b.issubmit!=4"); //过滤掉审核不通过的白卡出库记录
            if (OUTSTORAGEID != "")
                sbInfo.Append(" and a.OUTSTORAGEID<>'" + OUTSTORAGEID + "' ");//编辑时过滤掉当前信息

            sbInfo.Append(" group by a.cardno) d on c.cardno=d.cardno  ");
            sbInfo.Append(" where 1=1");
            //sbInfo.Append(" and nvl(d.issubmit,100)>3 ");//只获取未出库的信息
            sbInfo.Append(" and c.actualnumber-nvl(d.actualnumber,0)>0");//没有审批的盒号信息不显示
            sbInfo.Append(" and c.cardno not in(select * from v_getretcardno)");//过滤已经退货的盒号信息

            sbInfo.Append(" and (nvl(d.issubmit,0)>=4 or  d.issubmit is null)");

            sbInfo.Append(" order by c.boxno,c.cardno ");

            return DbHelperSQL.GetQueryResult(sbInfo.ToString());
        }
        #endregion
        #region 返回供应商、卡类型、盒号参数（离合修改需要盒号ID）
        /// <summary>
        /// 获取系统中有卡的 盒号、卡类型、供应商信息
        /// </summary>
        /// <param name="OUTSTORAGEID">编辑时需要</param>
        /// <returns></returns>
        public DataTable LhGetAllInfo(string OUTSTORAGEID)
        {
            StringBuilder sbInfo = new StringBuilder();
            sbInfo.Append(" select c.id,c.boxno,c.cardno,c.actualnumber-nvl(d.actualnumber,0) synum,");
            sbInfo.Append(" case length(d.lendno) when 8 then to_number(lendno)+1 else to_number(c.startno) end startno,");//获取下次出库开始卡号
            sbInfo.Append(" c.endno,c.Cardtypename,c.Cardtypeid,c.suppliersname,c.suppid,c.storageType,c.checkid from ");
            sbInfo.Append(" (select t.id,t.boxno,t.cardno,t.startno,t.endno,t.ACTUALNUMBER,t.Cardtypename,t.Cardtypeid,t.suppliersname,t.suppid,t.storagetype,t.checkid");
            sbInfo.Append(" from v_storage_CHECK_card_detailed t where t.issubmit=5");
            //入库类型不等于二次利用入库 //and t.storagetype !='AC537E666ADC46199AA742AAA5D43B2E' and t.storagetype !='4B1025E43FEE4F1D89FED74437D80974' and t.storagetype !='D0B4A3D7CE5D43F29048CA3692D4F56D' 缺卡入库 备品入库 赠品入库
            sbInfo.Append(" and (t.storagetype !='5DDA8F0A65AE4BDDA21A622EA6E1F257') ");
            sbInfo.Append(" ) c left join ");
            sbInfo.Append(" (select a.cardno,sum(a.actualnumber) actualnumber,nvl(max(a.endno),'') lendno,min(b.issubmit) issubmit from cardmaking_cardoutstrgedetail a");
            sbInfo.Append(" left join cardmaking_cardoutstorage b on a.outstorageid=b.id where b.issubmit!=4"); //过滤掉审核不通过的白卡出库记录
            if (OUTSTORAGEID != "")
                sbInfo.Append(" and a.OUTSTORAGEID<>'" + OUTSTORAGEID + "' ");//编辑时过滤掉当前信息

            sbInfo.Append(" group by a.cardno) d on c.cardno=d.cardno  ");
            sbInfo.Append(" where 1=1");
            //sbInfo.Append(" and nvl(d.issubmit,100)>3 ");//只获取未出库的信息
            sbInfo.Append(" and c.actualnumber-nvl(d.actualnumber,0)>0");//没有审批的盒号信息不显示
            sbInfo.Append(" and c.cardno not in(select * from v_getretcardno)");//过滤已经退货的盒号信息

            sbInfo.Append(" and (nvl(d.issubmit,0)>=4 or  d.issubmit is null)");

            sbInfo.Append(" order by c.boxno,c.cardno ");

            return DbHelperSQL.GetQueryResult(sbInfo.ToString());
        }
        #endregion

        #region 返回二次利用入库或者缺卡入库供应商、卡类型、盒号参数
        /// <summary>
        /// 获取系统中二次利用入库或者缺卡入库有卡的 盒号、卡类型、供应商信息
        /// </summary>
        /// <param name="outstorageid">编辑时需要</param>
        /// <returns></returns>
        public DataTable GetIsEcInfo(string outstorageid, string demandId)
        {
            StringBuilder sbInfo = new StringBuilder();
            sbInfo.Append(" select c.boxno,c.cardno,c.startno,c.actualnumber-nvl(d.actualnumber,0) synum,");
            sbInfo.Append(" c.endno,c.Cardtypename,c.Cardtypeid,c.suppliersname,c.suppid,c.storageType,c.checkid from ");
            sbInfo.Append(" (select t.boxno,t.cardno,t.startno,t.endno,t.ACTUALNUMBER,t.Cardtypename,t.Cardtypeid,t.suppliersname,t.suppid,t.storagetype,t.checkid");
            sbInfo.Append(" from v_storage_CHECK_card_detailed t where t.issubmit=5  ");
            //入库类型等于二次利用入库  缺卡入库 备品入库 赠品入库
            sbInfo.Append(" and (t.storagetype='5DDA8F0A65AE4BDDA21A622EA6E1F257' or t.storagetype ='AC537E666ADC46199AA742AAA5D43B2E' or t.storagetype ='4B1025E43FEE4F1D89FED74437D80974' or t.storagetype ='D0B4A3D7CE5D43F29048CA3692D4F56D')");
            sbInfo.Append(" ) c left join");
            sbInfo.Append(" (select a.cardno,sum(a.actualnumber) actualnumber,min(b.issubmit) issubmit from cardmaking_cardoutstrgedetail a");
            sbInfo.Append(" left join cardmaking_cardoutstorage b on a.outstorageid=b.id where b.issubmit!=4"); //过滤掉审核不通过的白卡出库记录
            if (outstorageid != "")
            {
                sbInfo.Append(" and demandId='" + demandId + "'");
                sbInfo.Append(" and a.OUTSTORAGEID ='" + outstorageid + "' ");//编辑时过滤掉当前信息
            }
            sbInfo.Append(" group by a.cardno) d on c.cardno=d.cardno  ");
            sbInfo.Append(" where 1=1");
            if (outstorageid == "")
                sbInfo.Append(" and c.actualnumber-nvl(d.actualnumber,0)>0");//没有审批的盒号信息不显示
            else
                sbInfo.Append(" and c.actualnumber-nvl(d.actualnumber,0)=0");//编辑时审批通过的盒号信息不显示
            sbInfo.Append(" order by c.boxno,c.cardno ");

            return DbHelperSQL.GetQueryResult(sbInfo.ToString());
        }
        #endregion
    }
}
